package com.szholly.plug.form.excel.web;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.IQueryMap;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.QueryFilter;
import com.szholly.data.general.QueryMap;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.ExcelTemplate;
import com.szholly.plug.form.excel.utils.StringUtil;

/**
 * 导入导出Excel文件的控制器
 */
@SuppressWarnings("deprecation")
@Controller
@RequestMapping("/excel/*")
public class ExcelController {

	protected Logger logger = LoggerFactory.getLogger(getClass());

	/**
	 * 生成并下载Excel文件。优先根据模板生成文档。
	 * 
	 * @param tableName
	 *            经过utf-8编码的数据库表名
	 * @param moduleName
	 *            经过utf-8编码的模块名称，用于填充标题和页名
	 * @param fileName
	 *            经过utf-8编码的文件名，用于命名下载文件，不需要解码
	 * @param template
	 *            经过utf-8编码的模板文件名，若为空则使用tableName作为模板文件名，若找不到模板文件则生成新的Excel文档
	 * @param queryFields
	 *            经过utf-8编码的要在excel文件中显示的字段，若有模板则此属性无效
	 * @param displayFields
	 *            经过utf-8编码的要在excel文件中显示的字段的显示名称，若有模板则此属性无效
	 * @param whereString
	 *            经过utf-8编码的sql where 子句
	 * @param orderbyString
	 *            经过utf-8编码的sql order by 子句
	 * @param selectFields
	 *            经过utf-8编码的sql select 子句
	 * @param groupByString
	 *            经过utf-8编码的sql group by 子句
	 * @param req
	 * @param res
	 * @param session
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping(value = "/export")
	public void export(@RequestParam(required = true) String tableName,
			@RequestParam(required = false) String moduleName,
			@RequestParam(required = true) String fileName,
			@RequestParam(required = false) String template,
			@RequestParam(required = false) String queryFields,
			@RequestParam(required = false) String displayFields,
			@RequestParam(required = false) String whereString,
			@RequestParam(required = false) String orderByString,
			@RequestParam(required = false) String selectFields,
			@RequestParam(required = false) String groupByString,
			@RequestParam(required = false) String jsonList,
			HttpServletRequest req, HttpServletResponse res, HttpSession session)
			throws Exception {
		String templatePath = session.getServletContext().getRealPath(
				"/WEB-INF/exceltemplate");
		File templateFile = null;
		template = template != null ? template : tableName;
		if (template != null) {
			templateFile = new File(templatePath + "\\" + template
					+ (template.endsWith(".xls") ? "" : ".xls"));
			if (!templateFile.exists()) {
				templateFile = new File(templatePath + "\\"
						+ tableName.toLowerCase() + ".xls");
				if (!templateFile.exists()) {
					templateFile = null;
				}
			}
		}
		List<RowBase> list = null;
		List<Map<String, String>> listObjects = null;
		if(null != jsonList || "".equals(jsonList)){
			listObjects = new ArrayList<Map<String, String>>();
			JSONArray jsonArray = JSONArray.fromObject(jsonList);
			for(int i = 0; i < jsonArray.size(); i++){
				JSONObject jsObj = jsonArray.getJSONObject(i);
				Iterator<String> iter = jsObj.keys();
				Map<String, String> obj = new HashMap<String, String>();
				while(iter.hasNext()){
					  String str = (String) iter.next();
					  if(null != queryFields && queryFields.indexOf(str)>-1){
						  String value = jsObj.get(str).toString();
						  obj.put(str, !value.equals("null")?value:"");
					  }else{
						  String value = jsObj.get(str).toString();
						  obj.put(str, !value.equals("null")?value:"");
					  }
				}
				if(obj.size()>0){
					listObjects.add(obj);
				}
			}
		}else{

			// 组织条件查询表数据
			HashMap<String, Object> whereMap = new HashMap<String, Object>();
			
			HashMap<String, Object> orderMap = new HashMap<String, Object>();

			ITableFactory tableFactory = (ITableFactory) SpringBeanFactory
					.getBean("TableFactory");
			ITable pTable = tableFactory.openTable(tableName);
			IQueryMap queryMap = new QueryMap();
			queryMap.setAddCaptionField(true);
			
			if (whereString != null) {
				JSONObject queryJsonObject = JSONObject.fromObject(whereString);
				whereMap = (HashMap<String, Object>) JSONObject.toBean(queryJsonObject, HashMap.class);
				//whereMap = JSONObject.fromObject(whereString);
//				filter.setWhereString(whereString);
			}
//			if (orderByString != null) {
//				filter.setOrderByString(orderByString);
//			}
//			if (selectFields != null) {
//				filter.setSelectFields(selectFields);
//			}
//			if (groupByString != null) {
//				filter.setGroupByString(groupByString);
//			}
			
			queryMap.setWhereMap(whereMap);
			queryMap.setSelectFields(selectFields);
			queryMap.setOrderByMap(orderMap);
			PageInfo pageInfo = new PageInfo();
			pageInfo.setPageIndex(1);
			pageInfo.setPageSize(50000);
			list = pTable.pageSelect(RowBase.class, queryMap,
					pageInfo);
		}


		fileName = java.net.URLEncoder.encode(fileName + ".xls", "utf-8");
		HSSFWorkbook wb = templateFile != null ? this.genExcelByTemplate(list,listObjects,
				moduleName, templateFile, null) : this.genExcel(list,
				moduleName, queryFields.split(","), displayFields.split(","));
		res.setHeader("Content-Disposition", "attachment; filename=\""
				+ fileName + "\"");
		res.setContentType("application/vnd.ms-excel; charset=gb2312");
		ServletOutputStream out = res.getOutputStream();
		wb.write(out);
		out.close();
	}

	/**
	 * 生成并下载Excel文件。优先根据模板生成文档。
	 * 
	 * @param tableName
	 *            经过utf-8编码的数据库表名
	 * @param moduleName
	 *            经过utf-8编码的模块名称，用于填充标题和页名
	 * @param fileName
	 *            经过utf-8编码的文件名，用于命名下载文件，不需要解码
	 * @param template
	 *            经过utf-8编码的模板文件名，若为空则使用tableName作为模板文件名，若找不到模板文件则生成新的Excel文档
	 * @param queryFields
	 *            经过utf-8编码的要在excel文件中显示的字段，若有模板则此属性无效
	 * @param displayFields
	 *            经过utf-8编码的要在excel文件中显示的字段的显示名称，若有模板则此属性无效
	 * @param whereString
	 *            经过utf-8编码的sql where 子句
	 * @param orderbyString
	 *            经过utf-8编码的sql order by 子句
	 * @param selectFields
	 *            经过utf-8编码的sql select 子句
	 * @param groupByString
	 *            经过utf-8编码的sql group by 子句
	 * @param req
	 * @param res
	 * @param session
	 * @throws Exception
	 */
	@RequestMapping(value = "/exportTable")
	public void exportTable(@RequestParam(required = true) String tableName,
			@RequestParam(required = false) String moduleName,
			@RequestParam(required = true) String fileName,
			@RequestParam(required = false) String template,
			@RequestParam(required = false) String queryFields,
			@RequestParam(required = false) String displayFields,
			@RequestParam(required = false) String whereString,
			@RequestParam(required = false) String orderByString,
			@RequestParam(required = false) String selectFields,
			@RequestParam(required = false) String groupByString,
			@RequestParam(required = false) String whereParameter,
			@RequestParam(required = false) String isNullHeJi,
			HttpServletRequest req, HttpServletResponse res, HttpSession session)
			throws Exception {
		String templatePath = session.getServletContext().getRealPath(
				"/WEB-INF/exceltemplate");
		File templateFile = null;
		template = template != null ? template : tableName;
		if (template != null) {
			templateFile = new File(templatePath + "\\" + template
					+ (template.endsWith(".xls") ? "" : ".xls"));
			if (!templateFile.exists()) {
				templateFile = new File(templatePath + "\\"
						+ tableName.toLowerCase() + ".xls");
				if (!templateFile.exists()) {
					templateFile = null;
				}
			}
		}
		QueryFilter filter = new QueryFilter();
		filter.setAddCaptionField(true);
		if (whereString != null) {
			filter.setWhereString(whereString);
		}
		if (orderByString != null) {
			filter.setOrderByString(orderByString);
		}
		if (selectFields != null) {
			filter.setSelectFields(selectFields);
		}
		if (groupByString != null) {
			filter.setGroupByString(groupByString);
		}
		if (whereParameter != null) {
			String[] strs = whereParameter.split(",");
			GlobalParameter[] para = new GlobalParameter[strs.length];
			for (int i = 0; i < strs.length; i++) {
				para[i] = StringUtil.getPara(strs[i].split(" ")[0],
						strs[i].split(" ")[1]);
			}
			filter.setWhereParameter(para);
		}
		// 获取自定义数据
		String exportInfo = req.getParameter("exportInfo");

		JSONArray jsArr = null;

		if (exportInfo != null) {
			jsArr = JSONArray.fromObject(exportInfo);
		}

		String[] fields;
		if (queryFields != null)
			fields = queryFields.split(",");
		else
			fields = selectFields.split(",");
		Double[] hj = new Double[fields.length];

		// 组织条件查询表数据
		HashMap<String, Object> whereMap = new HashMap<String, Object>();

		HashMap<String, Object> orderMap = new HashMap<String, Object>();

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory
				.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		IQueryMap queryMap = new QueryMap();
		queryMap.setAddCaptionField(true);
		queryMap.setWhereMap(whereMap);
		queryMap.setOrderByMap(orderMap);
		PageInfo pageInfo = new PageInfo();
		pageInfo.setPageIndex(1);
		pageInfo.setPageSize(50000);
		List<RowBase> list = pTable.pageSelect(RowBase.class, queryMap,
				pageInfo);
		// 计算合计行，并追加到最后一行
		for (int i = 0; i < list.size(); i++) {
			for (int j = 1; j < hj.length; j++) {
				if (hj[j] == null) {
					hj[j] = 0.0;
				}
				if (list.get(i).getValue(fields[j]) instanceof Double)
					hj[j] += (Double) list.get(i).getValue(fields[j]);
			}
		}
		// 添加合计行
		RowBase row = new RowBase();
		row.setValue(fields[0], "合计：");
		for (int i = 1; i < fields.length; i++) {
			if (hj[i] != 0)
				row.setValue(fields[i], hj[i]);
		}
		if (!"kong".equals(isNullHeJi)) {
			list.add(row);
		}
		fileName = java.net.URLEncoder.encode(fileName + ".xls", "utf-8");
		HSSFWorkbook wb = templateFile != null ? this.genExcelByTemplate(list,null,
				moduleName, templateFile, jsArr) : this.genExcel(list,
				moduleName, queryFields.split(","), displayFields.split(","));
		res.setHeader("Content-Disposition", "attachment; filename=\""
				+ fileName + "\"");
		res.setContentType("application/vnd.ms-excel; charset=gb2312");
		ServletOutputStream out = res.getOutputStream();
		wb.write(out);
		out.close();
	}

	/**
	 * 将xls文件中的内容导入到数据库中
	 * 
	 * @param tableName
	 *            表名
	 * @param defaultData
	 *            默认值，json形式字符串
	 * @param configFile
	 *            配置文件名
	 * @param req
	 * @param res
	 * @param session
	 * @throws IOException
	 * @throws FileUploadException
	 */
	@RequestMapping("/import.do")
	public void importHandller(@RequestParam(required = true) String tableName,
			@RequestParam(required = false) String defaultData,
			@RequestParam(required = false) String configFile,
			HttpServletRequest req, HttpServletResponse res, HttpSession session)
			throws IOException {
		Map<String, Object> result = new HashMap<String, Object>();
		Map<String, String> idMapping = null;// 用于存储旧-新id映射
		result.put("success", true);
		List<String> msgs = new ArrayList<String>();
		List<Integer> failedRows = new ArrayList<Integer>();
		int successCount = 0;
		int errorCount = 0;
		String templatePath = session.getServletContext().getRealPath(
				"/WEB-INF/exceltemplate");
		File templateFile = null;
		configFile = configFile != null ? configFile : tableName.toLowerCase();
		if (configFile != null) {
			templateFile = new File(templatePath + "\\" + configFile
					+ (configFile.endsWith(".json") ? "" : ".json"));
			if (!templateFile.exists()) {
				templateFile = new File(templatePath + "\\"
						+ tableName.toLowerCase() + ".json");
				if (!templateFile.exists()) {
					templateFile = null;
				}
			}
		}
		if (templateFile == null) {
			result.put("success", false);
			msgs.add("配置文件不存在");
		} else {
			try {
				ObjectMapper mapper = new ObjectMapper();
				JsonNode root = mapper.readTree(templateFile);
				int startRow = (root.path("startRow").asInt()) - 1;// excel的行从1开始，而poi从0开始
				String table = root.path("table").asText();
				Boolean replaceId = root.path("replaceId").asBoolean();
				if (replaceId == true) {
					idMapping = new HashMap<String, String>();
				}
				if (table == null || "".equals(table))
					table = tableName;
				JsonNode columns = root.path("columns");
				MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req;
				MultipartFile xlsFile = multipartRequest.getFile("file");
				HSSFWorkbook hssfWorkbook = new HSSFWorkbook(
						xlsFile.getInputStream());
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
				if (hssfSheet == null) {
					throw new IOException();
				}
				JsonNode defaultVals = null;
				if (defaultData != null)
					defaultVals = new ObjectMapper().readTree(defaultData);
				for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					if (hssfRow == null) {
						continue;
					}
					HashMap<String, Object> rowMap = new HashMap<String, Object>();
					for (JsonNode node : columns) {
						HSSFCell cell = hssfRow.getCell(node.path("index")
								.asInt());
						if (cell == null)
							continue;
						String cellValue = getHssfCellValue(cell);
						String field = node.path("field").asText()
								.toUpperCase();
						if (idMapping != null) {
							Boolean isIdField = node.path("isIdField")
									.asBoolean();
							if (isIdField == true) {
								if (cellValue != null && !"".equals(cellValue)) {
								}
							} else {
								Boolean isParentField = node.path(
										"isParentField").asBoolean();
								if (isParentField == true) {
									cellValue = idMapping.get(cellValue);
								}
							}
						}
						if (cellValue != null && !"".equals(cellValue)) {
							rowMap.put(field, cellValue);
						}
					}
					if (rowMap.size() > 0) {
						try {
							if (defaultVals != null) {// 填充默认字段值
								Iterator<Entry<String, JsonNode>> defaultMap = defaultVals
										.getFields();
								while (defaultMap.hasNext()) {
									Entry<String, JsonNode> kv = defaultMap
											.next();
									rowMap.put(kv.getKey(), kv.getValue()
											.asText());
								}
							}
							RowBase rowBase = new RowBase();
							Iterator<String> iter = rowMap.keySet().iterator();
							while (iter.hasNext()) {
								String key = iter.next();
								Object value = rowMap.get(key);
								rowBase.setValue(key, value);
							}
							ITableFactory tableFactory = (ITableFactory) SpringBeanFactory
									.getBean("TableFactory");
							ITable pTable = tableFactory.openTable(tableName);
							pTable.save(rowBase);
							successCount++;
						} catch (Exception e) {
							errorCount++;
							failedRows.add(rowNum + 1);
						}
					}
				}
			} catch (JsonProcessingException e) {
				result.put("success", false);
				msgs.add("配置文件存在错误");
				e.printStackTrace();
			} catch (JsonParseException e) {
				result.put("success", false);
				msgs.add("配置文件存在错误");
				e.printStackTrace();
			} catch (IOException e) {
				result.put("success", false);
				msgs.add("导入的Excel文件存在错误");
				e.printStackTrace();
			}
		}
		result.put("msgs", msgs);
		result.put("successCount", successCount);
		result.put("errorCount", errorCount);
		result.put("failedRows", failedRows);
		JSONObject json = JSONObject.fromObject(result);
		res.getWriter().write(
				("<script>result=" + json.toString() + "</script>"));
	}
	
	/**
	 * 不通过模板生成新的Excel文档
	 * 
	 * @param dataList
	 *            数据的RowBase集合
	 * @param moduleName
	 *            模块名称
	 * @param queryFields
	 *            要在excel文件中显示的字段
	 * @param displayFields
	 *            要在excel文件中显示的字段的显示名称
	 * @return HSSFWorkbook Excel文档对象
	 */
	private HSSFWorkbook genExcel(List<RowBase> dataList, String moduleName,
			String[] queryFields, String[] displayFields) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(moduleName == null ? "sheet1"
				: moduleName);
		HSSFRow row;
		HSSFCell cell;
		HSSFFont headerFont = wb.createFont();
		headerFont.setFontName("黑体");
		headerFont.setBoldweight((short) 150);
		headerFont.setFontHeightInPoints((short) 14);
		HSSFCellStyle headerStyle = wb.createCellStyle();
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headerStyle.setFont(headerFont);

		HSSFFont subheadFont = wb.createFont();
		subheadFont.setFontHeightInPoints((short) 11);
		HSSFCellStyle subheadStyle = wb.createCellStyle();
		subheadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		subheadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		subheadStyle.setFont(subheadFont);

		HSSFFont titleFont = wb.createFont();
		titleFont.setFontHeightInPoints((short) 11);
		titleFont.setBoldweight((short) 150);
		HSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		titleStyle
				.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setFont(titleFont);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);

		HSSFCellStyle contentStyle = wb.createCellStyle();
		contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

		row = sheet.createRow(0);
		row.setHeightInPoints(20);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				displayFields.length - 1));
		cell = row.createCell(0);
		cell.setCellValue(moduleName);
		cell.setCellStyle(headerStyle);

		row = sheet.createRow(1);
		row.setHeightInPoints(15);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				displayFields.length - 1));
		cell = row.createCell(0);
		cell.setCellValue("表格生成时间："
				+ DateFormatUtils.format(new Date(), "yyyy年MM月dd日 HH:mm:ss"));
		cell.setCellStyle(subheadStyle);

		row = sheet.createRow(2);
		row.setHeightInPoints(15);
		for (int i = 0; i < displayFields.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(displayFields[i]);
			cell.setCellStyle(titleStyle);
			sheet.setColumnWidth(i, 6000);
		}
		int count = 3;
		for (RowBase obj : dataList) {
			row = sheet.createRow(count++);
			for (int i = 0; i < queryFields.length; i++) {
				String key = queryFields[i];
				cell = row.createCell(i);
				Object value = obj.get(key);
				if (value == null) {
					value = obj.get(key);
				}
				if (value != null && !value.equals("")) {
					if (StringUtil.isNumber(value.toString())) {
						cell.setCellValue(Double.parseDouble(value.toString()));
					} else {
						cell.setCellValue(value.toString());
					}
				}
				cell.setCellStyle(contentStyle);
			}
		}
		return wb;
	}

	/**
	 * 根据模板生成excel文档
	 * 
	 * @param list
	 *            要在excel文件中显示的字段的显示名称
	 * @param moduleName
	 *            模块名称
	 * @param templateFile
	 *            模板文件
	 * @return HSSFWorkbook Excel文档对象
	 * @throws Exception
	 */
	private HSSFWorkbook genExcelByTemplate(List<RowBase> list,List<Map<String, String>> listObjects,
			String moduleName, File templateFile, JSONArray jsArr)
			throws Exception {
		List<Map<String, String>> exportObjects = new ArrayList<Map<String, String>>();
		if(null != listObjects){
			exportObjects = listObjects;
		}else{
			for (RowBase rowBase : list) {
				Map<String, String> obj = new HashMap<String, String>();
				String columns[] = rowBase.getColumns();
				for(String column : columns){
					obj.put(column,String.valueOf(rowBase.get(column)));
				}

				exportObjects.add(obj);
			}
		}

		String templateFilePath = templateFile.getAbsolutePath();
		String outputFile = templateFile.getParentFile().getAbsolutePath()
				+ "temp";
		ExcelTemplate template = new ExcelTemplate(templateFilePath, outputFile);
		template.setMaxRow(5000);
		return template.getWorkbook(exportObjects, jsArr);
	}

	/**
	 * 获取单元格内容
	 * 
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings("static-access")
	private String getHssfCellValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			// 返回数值类型的值
			Double v = hssfCell.getNumericCellValue();
			double dv = Math.abs(v - (double) v.longValue());
			if (dv < 0.000001)
				return String.valueOf(v.longValue());
			else
				return String.valueOf(v);
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
	/**
	 * 将xls文件中的内容导入到数据库中
	 * 
	 * @param tableName
	 *            表名
	 * @param defaultData
	 *            默认值，json形式字符串
	 * @param configFile
	 *            配置文件名
	 * @param req
	 * @param res
	 * @param session
	 * @throws IOException
	 * @throws FileUploadException
	 */
	@RequestMapping("/importMapExcel.do")
	public void importMapExcel(HttpServletRequest req, 
			HttpServletResponse res, HttpSession session)throws IOException {
		Map<String, Object> result = new HashMap<String, Object>();
		List<HashMap<String,String>> kqdList = new ArrayList<>();
		result.put("success", true);
		try {
			MultipartResolver resolver = new CommonsMultipartResolver(req.getSession().getServletContext());
			MultipartHttpServletRequest multipartRequest = resolver.resolveMultipart(req);
			MultipartFile xlsFile = multipartRequest.getFile("file");
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(
					xlsFile.getInputStream());
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
			if (hssfSheet == null) {
				throw new IOException();
			}
			
//			HSSFRow keyRow = hssfSheet.getRow(0);
//			if(keyRow == null)
//				return;
			List<String> keyList = new ArrayList<>();
//			Iterator<Cell> keyIter = keyRow.cellIterator();
//			while(keyIter.hasNext())
//				keyList.add(keyIter.next().toString());
			//拐点号
			keyList.add("gdh");
			//x坐标(起标高)
			keyList.add("x");
			//y坐标(止标高)
			keyList.add("y");
			//矿体标识
			keyList.add("ktbz");
			//性质
			keyList.add("xz");
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				HashMap<String, String> rowMap = new HashMap<String, String>();
				for(int i=0;i<keyList.size();i++){
					if(keyList.get(i)!=null && hssfRow.getCell(i)!=null)
						rowMap.put(keyList.get(i), hssfRow.getCell(i).toString());
				}
				rowMap.put("xh",rowNum+"");
				kqdList.add(rowMap);
			}
		} catch (IOException e) {
			result.put("success", false);
			e.printStackTrace();
		}
		result.put("value", kqdList);
		String json = JSONObject.fromObject(result).toString();
		// 向浏览器发送一个响应头，设置浏览器的解码方式为UTF-8
		res.setHeader("Content-type", "text/html;charset=UTF-8");
		OutputStream stream = res.getOutputStream();
		stream.write(json.getBytes("UTF-8"));
//		res.getWriter().write(
//				("<script>result=" + json.toString() + "</script>"));
	}
}